package t;
import java.sql.*;
import java.util.Random;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class makeTest {
	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost:3306/websql";
	private String user = "websql";
	private String passwd = "123456";
	private Connection con;
	public String msg;
	public boolean flag = false;
	private ResultSet rs;
	
	public makeTest(){
		this.getConnection();
	}
	
	private void getConnection(){
		try{
			Class.forName(driver);
			con = DriverManager.getConnection(url,user,passwd);
			flag = true;
		}
		catch(Exception e){
			System.out.println(e.getMessage());
			msg = "aaa#"+msg+e.getMessage();
		}
	}
	
	public String depositData(String User,String su,String an){ //将考生试卷的回答连同试题一同存入数据库
		String sid = changeUserToSid(User);
		String[] subject = su.split("@");
		String[] anser = an.split("@");
		String examName = "";
		//int len = (subject.length>anser.length ? anser.length:subject.length);
		try{
			PreparedStatement insert = con.prepareStatement("insert into subject values(?,?,?,?,?)");
			Statement sta = con.createStatement();
			rs = sta.executeQuery("select * from recent");
			if(rs.next()) examName = rs.getString(1);
			for(int i=0;i<subject.length;i++){
				insert.setString(1, examName);
				insert.setString(2, sid);
				insert.setString(3, subject[i]);
				insert.setString(4, anser[i]);
				insert.setFloat(5, -1);
				insert.execute();
			}
		}
		catch(Exception e){
			System.out.println(e.getMessage());
			msg = "bbb#"+msg+e.getMessage();
			return e.getMessage();
		}
		
		return "交卷成功!";
	}
	
	public String depositHomework(String partName,String User,String partData,String anserData,String t){
		String[] subject = partData.split("@");
		String[] anser = anserData.split("@");
		String[] time = t.split("@");
		String sid = changeUserToSid(User);
		try{
			PreparedStatement insert = con.prepareStatement("insert into practise values(?,?,?,?,?,?,?)");
			for(int i=0;i<subject.length-1;i++){
				insert.setString(1, partName);
				insert.setString(2, sid);
				insert.setString(3, subject[i]);
				insert.setString(4, anser[i]);
				insert.setInt(5,Integer.valueOf(time[i]));
				insert.setFloat(6,-1 );
				insert.setInt(7,0);
				insert.execute();
			}
		}
		catch(Exception e){
			return e.getMessage();
		}
		return "ok";
	}
	
	public String updateSubject(String examName,String sid,String subject,String anser,String score){ //老师批阅后更新对应同学的数据
		int wap ;
		String res="";
		try{
			String[] s2 = subject.split("@");
			String[] s4 = score.split("@");   //where examName= ? and sid= ? and subjectData= ?
			PreparedStatement insert = con.prepareStatement("update subject set score= ? where examName= ? and sid= ? and subjectData= ?");
			res = "111";
			for(int i=0;i<s2.length;i++){
					wap = Integer.parseInt(s4[i]);
					
					res = res+wap;
					insert.setInt(1, wap);
					insert.setString(2, examName);
					insert.setString(3, sid);
					insert.setString(4, s2[i]);
					insert.addBatch();
			}
			res = res+"end"+examName+sid;
			int[] x = insert.executeBatch();
			String str = "";
			for(int i=0;i<x.length;i++)str = str+x[i];
			res = res+"xxx"+str;
		}
		catch(Exception e){
			return "fail   "+res+e.getMessage();
		}
		return "ok";
	}
	
	public String updatePractiseData(String partName,String sid,String subject,String anser,String score,String time,String grade){ //老师批阅后更新对应同学的数据
		int wap2,wap3 ,wap4;
		String res="";
		try{
			String[] s1 = subject.split("@");
			String[] s2 = time.split("@"); 
			String[] s3 = score.split("@");
			String[] s4 = grade.split("grade");
			PreparedStatement insert = con.prepareStatement("update practise set whenLong=?,score=?,grade=? where partName= ? and sid= ? and partData= ?");
			res = "111";
			for(int i=0;i<s2.length;i++){
					wap2= Integer.parseInt(s2[i]);
					wap3 = Integer.parseInt(s3[i]);
					wap4 = Integer.parseInt(s4[i]);
					insert.setInt(1, wap2);
					insert.setInt(2, wap3);
					insert.setInt(3, wap4);
					insert.setString(4, partName);
					insert.setString(5, sid);
					insert.setString(6, s1[i]);
					insert.addBatch();
			}
			int[] x = insert.executeBatch();
			String str = "";
			for(int i=0;i<x.length;i++)str = str+x[i];
			res = res+"xxx"+str;
		}
		catch(Exception e){
			return "fail   "+res+e.getMessage();
		}
		return "ok";
	}
	
	public String getData(String User,String kinds){ //改卷时根据所选择学生学号从数据库取出其对应的题目或回答
		String sid = changeUserToSid(User);
		if(sid==null) return "失败！";
		String res="";
		ResultSet rs;
		String kind = "subjectData";
		if(kinds.equals("anser")) kind = "anserData";
		try{
			Statement sta = con.createStatement();
			rs = sta.executeQuery("select "+kind+" from subject where sid='"+sid+"'");
			while(rs.next()){
				res = res+rs.getString(1)+"#";
			}
		}
		catch(Exception e){
			msg = "ccc#"+msg+e.getMessage();
		}
		return res;
	}
	
	public String selectHomework(){
		String res = "";
		try{
			Statement s = con.createStatement();
			ResultSet rs =  s.executeQuery("select partName,endTime from part");
			while(rs.next()) res = res+rs.getString(1)+"#"+rs.getString(2)+"@";
			
		}
		catch(Exception e){
			return e.getMessage();
		}
		return res;
	}
	
	public String getPractiseStumsg(){
		String res = "";
		try{
			Statement s = con.createStatement();
			ResultSet rs =  s.executeQuery("select partName,practise.sid,name,sum(score),avg(grade) from practise,login where practise.sid=login.sid group by partName,sid,name,score");
			while(rs.next()) res = res+rs.getString(1)+"#"+rs.getString(2)+"#"+rs.getString(3)+"#"+rs.getString(4)+"#"+rs.getString(5)+"@";
		}
		catch(Exception e){
			return e.getMessage();
		}
		return res;
	}
	
	public String getDataFromPractise(String partName,String sid){
		String subject="",anser="",time="",score="",grade="",res="";
		try{
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery("select * from practise where partName='"+partName+"' and sid='"+sid+"'");
			while(rs.next()){
				subject = subject +rs.getString(3)+"#";
				anser = anser +rs.getString(4)+"#";
				time = time +rs.getString(5)+"#";
				score = score +rs.getString(6)+"#";
				grade = grade +rs.getString(7)+"#";
			}
			res = subject+"@"+anser+"@"+time+"@"+score+"@"+grade;
		}
		catch(Exception e){
			res = e.getMessage();
		}
		return res;
	}
	public String selectPartData(String name){
		String res = "";
		try{
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery("select data from partData where partName='"+name+"'");
			while(rs.next()) res = res+rs.getString(1)+"#";
		}
		catch(Exception e){
			return e.getMessage();
		}
		
		return res;
	}
	
	public String changeUserToSid(String User){
		String sid="";
		try{
			Statement sta = con.createStatement();
			ResultSet rs =  sta.executeQuery("select sid from login where User='"+User+"'");
			if(rs.next()) sid = rs.getString(1);
			else sid = "xxx";
		}
		catch(Exception e){
			sid = e.getMessage();
		}
		
		return sid;
	}
	
	public String changeSidToUser(String sid){
		String User="";
		try{
			Statement sta = con.createStatement();
			ResultSet rs =  sta.executeQuery("select User from login where sid='"+sid+"'");
			if(rs.next()) User = rs.getString(1);
			else User = "xxx";
		}
		catch(Exception e){
			User = e.getMessage();
		}
		
		return User;
	}
	public String distributeSubject(){ //从题库中随机抽取指定数目的题目
		String examName = "";
		String res="";
		String[] swap;
		ResultSet rs;
		Random r = new Random();
		int c=0;
		int counts = 0;
		try{
			Statement s = con.createStatement();
			rs = s.executeQuery("select * from recent"); //获取正在进行的考试的考试名称
			if(rs.next()) examName = rs.getString(1);
			rs = s.executeQuery("select subjectCounts from exam where examName='"+examName+"'"); //获取此次考试的相关信息：每个考生多少道题
			if(rs.next()) counts = rs.getInt(1);
			rs = s.executeQuery("select count(*) from dataBank where examName='"+examName+"'");
			if(rs.next()) c = rs.getInt(1);
			if(counts>c) counts = c; 
			 //抽题
			rs = s.executeQuery("select data from dataBank where examName='"+examName+"'");
			while(rs.next()) res = res+rs.getString(1)+"#";
			swap = res.split("#");
			res = "";
			int[] ran = random(c,counts);
			for(int i=0;i<counts;i++) res = res+swap[ran[i]]+"#";
		}
		catch(Exception e){
			res = res+"ddd#"+msg+e.getMessage();
		}
		return res;
	}
	
	private int[] random(int total,int counts){
		int[] res = new int[counts];
		int x = total/counts;
		Random r = new Random();
		int swap = 0;
		boolean flag = true;
		for(int i=0;i<counts;i++){
			swap = r.nextInt(total);
			for(int j=0;j<i;j++)
				if(swap==res[j]){
					flag = false;
					break;
				}
			if(flag) res[i] = swap;
			else i--;
			flag = true;
		}
		return res;
	}
	
	public String createAnExam(String name,String stime,String etime,int whenLong,int counts){ //将创建的一场考试的详细信息记录下来
		Timestamp startTime;
		Timestamp endTime;
		try{
			startTime = Timestamp.valueOf(stime);
			endTime = Timestamp.valueOf(etime);
			PreparedStatement insert = con.prepareStatement("insert into exam values(?,?,?,?,?)");
			insert.setString(1, name);
			insert.setTimestamp(2, startTime);
			insert.setTimestamp(3, endTime);
			insert.setInt(4, whenLong);
			insert.setInt(5, counts);
			insert.execute();
		
		}
		catch(Exception e){
			return e.getMessage();
		}
		return "ok";
	}
	
	public String createPractise(String name,String time){
		Timestamp end;
		try{
			end = Timestamp.valueOf(time);
			PreparedStatement insert = con.prepareStatement("insert into part values(?,?)");
			insert.setString(1, name);
			insert.setTimestamp(2, end);
			insert.execute();
		}
		catch(Exception e){
			return e.getMessage();
		}
		return "ok";
	}
	
	public String addExamData(String name,String Data){ //将创建的考试的题目存入数据库
		Matcher m = Pattern.compile("[^#{1}/*{1}#{1}]+").matcher(Data);
		try{
			PreparedStatement insert = con.prepareStatement("insert into dataBank values(?,?)");
			while(m.find()){
				insert.setString(1, name);
				insert.setString(2, m.group());
				insert.execute();
			}
			Statement sta = con.createStatement();
			sta.execute("delete from recent");
			sta.execute("insert into recent values('"+name+"')");
		}
		catch(Exception e){
			return e.getMessage();
		}
		return "ok";
	}
	
	public String addPractiseData(String name,String Data){ //将创建的练习作业的题目存入数据库
		Matcher m = Pattern.compile("[^#{1}/*{1}#{1}]+").matcher(Data);
		try{
			PreparedStatement insert = con.prepareStatement("insert into partData values(?,?)");
			while(m.find()){
				insert.setString(1, name);
				insert.setString(2, m.group());
				insert.execute();
			}
		}
		catch(Exception e){
			return e.getMessage();
		}
		return "ok";
	}
	
	public String createEnvironment(String sql){ //为每位考生在其账户数据库下创建本次考试的环境，添加数据等;
		String[] data = sql.split(";");
		String[] user;
		String str = "";
		Statement sta;
		int i;
		try{
			sta = con.createStatement();
			ResultSet rs = sta.executeQuery("select User from login");
			while(rs.next()) str =str+rs.getString(1)+"#";
			user = str.split("#");
		}
		catch(Exception ex){
			return ex.getMessage();
		}
		String msg="";
		for(i=0;i<user.length;i++){
			try{
				sta.addBatch("use "+user[i]);
				for(int j=0;j<data.length;j++) sta.addBatch(data[j]);
				sta.executeBatch();
				
			}
			catch(Exception e){
				msg = msg + e.getMessage();
			}
		}
		return msg;
	}
	
	
}
